CREATE PROCEDURE SP_SAVE_PRICE_SETUP_DETAILS
(
 @MRKT_PART_NUMBER VARCHAR(50),
 @QUARTER		   INT,
 @YEAR			   INT,
 @RANGE_FROM       INT,
 @RANGE_TO		   INT,
 @REGULAR_PRICE    FLOAT,
 @SAMPLE_PRICE     FLOAT,
 @DELETE_FLAG	   INT	
)
AS
BEGIN

		DECLARE @PRODUCT_ID INT
		DECLARE @error_string VARCHAR(100)
		DECLARE @QUARTER_ID   INT
		--SELECT *FROM PRODUCT_PRICE_TABLE
		--SELECT * FROM QUARTER_PRICING_TABLE
		SELECT @PRODUCT_ID = PRODUCT_ID FROM PRODUCT P,PROCESS_STEP PS WHERE P.PART_NUMBER = @MRKT_PART_NUMBER AND P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'FINISHED GOODS'
		IF @PRODUCT_ID IS NULL
		BEGIN
			SELECT @PRODUCT_ID = PRODUCT_ID FROM PRODUCT P,PROCESS_STEP PS, PRODUCT_MAP_TABLE PMT WHERE PMT.PART_NUMBER = @MRKT_PART_NUMBER AND P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PMT.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'FINISHED GOODS'
			IF @PRODUCT_ID IS NULL
			BEGIN
					SET @error_string = 'PART NUMBER NOT FOUND IN CHIPTRAC SYSTEM: "' + @MRKT_PART_NUMBER + '"'
					RAISERROR( @error_string, 16, 1 ) WITH LOG
					RETURN
			END
		END

		select @QUARTER_ID = CQC_Quarter_ID from Company_Quarterly_Calendar where CQC_YearID = (select CC_YearID from  Company_Calendar where CC_Year = @YEAR ) AND CQC_Quarter_No = @QUARTER

		IF @DELETE_FLAG = 0
		BEGIN
				DELETE FROM PRODUCT_PRICE_TABLE WHERE PRODUCT_ID = @PRODUCT_ID
				DELETE FROM QUARTER_PRICING_TABLE WHERE PRODUCT_ID = @PRODUCT_ID AND QUARTER_ID = @QUARTER_ID
		END
			
		INSERT INTO PRODUCT_PRICE_TABLE (PRODUCT_ID, PPT_QTY_FROM, PPT_QTY_TO, PPT_UNIT_PRICE, PPT_SET_BY_ID, TIMESTAMP, IS_SAMPLE)
					VALUES ( @PRODUCT_ID, @RANGE_FROM, @RANGE_TO, @REGULAR_PRICE, 'Admin',CURRENT_TIMESTAMP, 1)

		INSERT INTO PRODUCT_PRICE_TABLE (PRODUCT_ID, PPT_QTY_FROM, PPT_QTY_TO, PPT_UNIT_PRICE, PPT_SET_BY_ID, TIMESTAMP, IS_SAMPLE)
					VALUES ( @PRODUCT_ID, @RANGE_FROM, @RANGE_TO, @SAMPLE_PRICE, 'Admin',CURRENT_TIMESTAMP, 0 )

		INSERT INTO QUARTER_PRICING_TABLE (QUARTER_ID, PRODUCT_ID, QPT_QTY_FROM, QPT_QTY_TO,QPT_Unit_PRICE, QPT_SET_BY_ID,TIMESTAMP, IS_SAMPLE)
					VALUES ( @QUARTER_ID, @PRODUCT_ID, @RANGE_FROM, @RANGE_TO, @REGULAR_PRICE, 'Admin',CURRENT_TIMESTAMP, 1)

		INSERT INTO QUARTER_PRICING_TABLE (QUARTER_ID, PRODUCT_ID, QPT_QTY_FROM, QPT_QTY_TO, QPT_Unit_PRICE, QPT_SET_BY_ID,TIMESTAMP, IS_SAMPLE)
					VALUES ( @QUARTER_ID, @PRODUCT_ID, @RANGE_FROM, @RANGE_TO, @SAMPLE_PRICE, 'Admin',CURRENT_TIMESTAMP, 0 )	

END
